package com.sls.nzdapplication.util;

import android.content.Context;


import com.sls.nzdapplication.myapp.ZDAPP;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelUtils {
    public static WritableFont arial14font = null;

    public static WritableCellFormat arial14format = null;
    public static WritableFont arial10font = null;
    public static WritableCellFormat arial10format = null;
    public static WritableFont arial12font = null;
    public static WritableCellFormat arial12format = null;

    public final static String UTF8_ENCODING = "UTF-8";
    public final static String GBK_ENCODING = "GBK";


    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    public static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);//对齐格式
            arial12format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); //设置边框

        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    public static void initExcel(String filePath, String sheetName, String[] colName) {
        format();
        //创建一个工作薄，就是整个Excel文档
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            //使用Workbook创建一个工作薄，就是整个Excel文档
            workbook = Workbook.createWorkbook(file);
            //设置表格的名称(两个参数分别是工作表名字和插入位置，这个位置从0开始)
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            //创建label标签：实际就是单元格的标签（三个参数分别是：col + 1列，row + 1行， 内容， 单元格格式）
//            Label label = new Label(0, 0, filePath, arial14format);//设置第一行的单元格标签为：标题
            //将标签加入到工作表中
//            sheet.addCell(label);

            //通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
            // 表示将从第x+1列，y+1行到m+1列，n+1行合并 (四个点定义了两个坐标，左上角和右下角)
            sheet.mergeCells(0, 0, colName.length - 1, 1);
            sheet.addCell(new Label(0, 0, sheetName, arial14format));
            sheet.setRowView(0, 520);

            //再同一个单元格中写入数据，上一个数据会被下一个数据覆盖
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 2, colName[col], arial10format));
            }
            //设置行高 参数的意义为（第几行， 行高）
            sheet.setRowView(2, 340);
            workbook.write();// 写入数据
        } catch (IOException | WriteException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();// 关闭文件
                } catch (IOException | WriteException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void initExcel4dy(String filePath, String sheetName, String[] colName) {
        format();
        //创建一个工作薄，就是整个Excel文档
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            //使用Workbook创建一个工作薄，就是整个Excel文档
            workbook = Workbook.createWorkbook(file);
            //设置表格的名称(两个参数分别是工作表名字和插入位置，这个位置从0开始)
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            //创建label标签：实际就是单元格的标签（三个参数分别是：col + 1列，row + 1行， 内容， 单元格格式）
//            Label label = new Label(0, 0, filePath, arial14format);//设置第一行的单元格标签为：标题
            //将标签加入到工作表中
//            sheet.addCell(label);

            //通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
            // 表示将从第x+1列，y+1行到m+1列，n+1行合并 (四个点定义了两个坐标，左上角和右下角)
            sheet.mergeCells(0, 0, colName.length - 1, 0);
            sheet.addCell(new Label(0, 0, sheetName, arial14format));
            sheet.setRowView(0, 520);

            //再同一个单元格中写入数据，上一个数据会被下一个数据覆盖
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 1, colName[col], arial10format));
            }
            //设置行高 参数的意义为（第几行， 行高）
            sheet.setRowView(1, 340);
            workbook.write();// 写入数据
        } catch (IOException | WriteException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();// 关闭文件
                } catch (IOException | WriteException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void initExcel4gt(String filePath, String sheetName, String[] colName) {
        format();
        //创建一个工作薄，就是整个Excel文档
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            //使用Workbook创建一个工作薄，就是整个Excel文档
            workbook = Workbook.createWorkbook(file);
            //设置表格的名称(两个参数分别是工作表名字和插入位置，这个位置从0开始)
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            //创建label标签：实际就是单元格的标签（三个参数分别是：col + 1列，row + 1行， 内容， 单元格格式）
//            Label label = new Label(0, 0, filePath, arial14format);//设置第一行的单元格标签为：标题
            //将标签加入到工作表中
//            sheet.addCell(label);

            //通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
            // 表示将从第x+1列，y+1行到m+1列，n+1行合并 (四个点定义了两个坐标，左上角和右下角)
//            sheet.mergeCells(0, 0, colName.length - 1, 0);
//            sheet.addCell(new Label(0, 0, sheetName, arial14format));
//            sheet.setRowView(0, 520);

            //再同一个单元格中写入数据，上一个数据会被下一个数据覆盖
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }
            //设置行高 参数的意义为（第几行， 行高）
            sheet.setRowView(0, 340);
            workbook.write();// 写入数据
        } catch (IOException | WriteException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();// 关闭文件
                } catch (IOException | WriteException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    public static void initExcelZY(String filePath) {
        format();
        //创建一个工作薄，就是整个Excel文档
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }

            workbook = Workbook.createWorkbook(file);
            for(int i = 0; i< ZDAPP.EXCEL_ZYMB_SHEET.length; i++){
                WritableSheet sheet = workbook.createSheet(ZDAPP.EXCEL_ZYMB_SHEET[i], i);
                String[] colName = ZDAPP.ZY_MODEL_TITLE.get(i);
                sheet.mergeCells(0, 0, colName.length - 1, 0);
                sheet.addCell(new Label(0, 0, ZDAPP.EXCEL_ZYMB_SHEET[i], arial14format));
                sheet.setRowView(0, 520);
                for (int col = 0; col < colName.length; col++) {
                    sheet.addCell(new Label(col, 1, colName[col], arial10format));
                }
                sheet.setRowView(1, 340);
            }

            workbook.write();// 写入数据
        } catch (IOException | WriteException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();// 关闭文件
                } catch (IOException | WriteException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    public static void initExcelModel(String filePath, String sheetName) {
        format();
        //创建一个工作薄，就是整个Excel文档
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }

            workbook = Workbook.createWorkbook(file);
            for(int i = 0;i< ZDAPP.EXCEL_DYMB_SHEET.length;i++){
                WritableSheet sheet = workbook.createSheet(ZDAPP.EXCEL_DYMB_SHEET[i], i);
                String[] colName = ZDAPP.MODEL_TITLE.get(i);
                for (int col = 0; col < colName.length; col++) {
                    sheet.addCell(new Label(col, 0, colName[col], arial10format));
                }
                sheet.setRowView(0, 340);
            }

            workbook.write();// 写入数据
        } catch (IOException | WriteException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();// 关闭文件
                } catch (IOException | WriteException e) {
                    e.printStackTrace();
                }
            }
        }

        for(int i = 0;i< ZDAPP.EXCEL_DYMB_SHEET.length;i++){
            writeObjListToExcel(Arrays.asList(ZDAPP.MODEL_EXPLANATION.get(i)),filePath,i);
        }
    }

    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(List<String> objList,String fileName,int s) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),workbook);
                WritableSheet sheet = writebook.getSheet(s);

//              sheet.mergeCells(0,1,0,objList.size()); //合并单元格
//              sheet.mergeCells()
                for (int i = 0; i < objList.size(); i++) {
                    sheet.addCell(new Label(i,  1, objList.get(i),arial12format));
                    if (objList.get(i).length() <= 5){
                        sheet.setColumnView(i,objList.get(i).length()+8); //设置列宽
                    }else {
                        sheet.setColumnView(i,objList.get(i).length()+5); //设置列宽
                    }
                }
                sheet.setRowView(1,340); //设置行高


                writebook.write();
//                Toast.makeText(c, "导出到手机存储中文件夹Record成功", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }

    public static <T> void writeObjListToExcel4Model(List<T> objList,String fileName,int s) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),workbook);
                WritableSheet sheet = writebook.getSheet(s);

//              sheet.mergeCells(0,1,0,objList.size()); //合并单元格
//              sheet.mergeCells()

                for (int j = 0; j < objList.size(); j++) {
                    ArrayList<String> list = (ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 2, list.get(i),arial12format));
                        if (list.get(i).length() <= 5){
                            sheet.setColumnView(i,list.get(i).length()+8); //设置列宽
                        }else {
                            sheet.setColumnView(i,list.get(i).length()+5); //设置列宽
                        }
                    }
                    sheet.setRowView(j+2,350); //设置行高
                }

                writebook.write();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }

    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(List<T> objList,String fileName, Context c) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),workbook);
                WritableSheet sheet = writebook.getSheet(0);

//              sheet.mergeCells(0,1,0,objList.size()); //合并单元格
//              sheet.mergeCells()

                for (int j = 0; j < objList.size(); j++) {
                    ArrayList<String> list = (ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 3, list.get(i),arial12format));
                        if (list.get(i).length() <= 5){
                            sheet.setColumnView(i,list.get(i).length()+8); //设置列宽
                        }else {
                            sheet.setColumnView(i,list.get(i).length()+5); //设置列宽
                        }
                    }
                    sheet.setRowView(j+3,350); //设置行高
                }

                writebook.write();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }

    public static <T> void writeObjListToExcel4dy(List<T> objList,String fileName, Context c) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),workbook);
                WritableSheet sheet = writebook.getSheet(0);

//              sheet.mergeCells(0,1,0,objList.size()); //合并单元格
//              sheet.mergeCells()

                for (int j = 0; j < objList.size(); j++) {
                    ArrayList<String> list = (ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 2, list.get(i),arial12format));
                        if (list.get(i).length() <= 5){
                            sheet.setColumnView(i,list.get(i).length()+8); //设置列宽
                        }else {
                            sheet.setColumnView(i,list.get(i).length()+5); //设置列宽
                        }
                    }
                    sheet.setRowView(j+2,350); //设置行高
                }

                writebook.write();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }

    public static <T> void writeObjListToExcel4gt(List<T> objList,String fileName, Context c) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),workbook);
                WritableSheet sheet = writebook.getSheet(0);

//              sheet.mergeCells(0,1,0,objList.size()); //合并单元格
//              sheet.mergeCells()

                for (int j = 0; j < objList.size(); j++) {
                    ArrayList<String> list = (ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 1, list.get(i),arial12format));
                        if (list.get(i).length() <= 5){
                            sheet.setColumnView(i,list.get(i).length()+8); //设置列宽
                        }else {
                            sheet.setColumnView(i,list.get(i).length()+5); //设置列宽
                        }
                    }
                    sheet.setRowView(j+1,350); //设置行高
                }

                writebook.write();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }
}
